<?php

/**
 * 头部文件
 * 2014-7-31 by sutroon
 */
session_start();
header("Content-Type: text/html;charset=utf-8");
$action = I('action');

$ln = PHP_EOL;
$tab = "\t";

// 表单请求处理 2014-7-25 by sutroon
if ($action == 'logindb') {
    // 登录数据库
    $arr['host'] = I('host');
    $arr['user'] = I('user');
    $arr['password'] = I('password');
    $arr['database'] = I('database');
    $_SESSION['conninfo'] = $arr;
    header('location:?success');
} else if ($action == 'loginoff') {
    // 注销连接
    unset($_SESSION['conninfo']);
    header('location:?success');
}

// 获取数据库连接
$conn_info = isset($_SESSION['conninfo']) ? $_SESSION['conninfo'] : null;
$database = '';
if ($conn_info) {
    $database = $conn_info['database'];
    open_conn($conn_info['host'], $conn_info['user'], $conn_info['password'], $database);
}

// =============================通用函数1=====================================
/**
 * 打开数据库连接
 * @param string $host
 * @param string $user
 * @param string $password
 * @param string $database
 * @return resource
 * @since 1.0 2014-9-9 by sutroon
 */
function open_conn($host, $user, $password, $database)
{
    @$db = mysql_connect($host, $user, $password);
    if ($db === false) {
        die(mysql_error() . '<br /><a href="?action=loginoff">Back</a>');
    }
    mysql_select_db($database);
    mysql_query("set names 'utf8'");
    return $db;
}

/**
 * 获取表单参数
 * @param string $name
 * @param mixed $defv
 * @return mixed
 * @since 1.0 2014-7-25 by sutroon
 */
function I($name, $defv = '')
{
    if (isset($_GET[$name])) {
        return $_GET[$name];
    }
    if (isset($_POST[$name])) {
        return $_POST[$name];
    }
    return $defv;
}

/**
 * 替换换行符和缩进符并进行Html编码
 * @param string $str
 * @param string $ln
 * @param string $tab
 * @return string
 * @since 1.0 2014-9-1 by sutroon
 */
function sohtmlencode($str, $ln = '<br />', $tab = '&nbsp;&nbsp;&nbsp;&nbsp;')
{
    return str_replace('{ln}', $ln, str_replace('{tab}', $tab, htmlspecialchars($str)));
}

function sotextareaencode($str)
{
    $ln = PHP_EOL;
    $tab = "\t";
    return str_replace('{ln}', $ln, str_replace('{tab}', $tab, $str));
}

/**
 * 检测字符串是否已指定的字符串结尾
 * @param string $str
 * @param string $search
 * @param boolean $case_insensitivity 是否忽略大小写区别(默认不忽略)
 * @return boolean
 * @example end_with('my words','ds');
 * @since 1.0.0 2013-10-7 sutroon
 */
function sofunc_end_with($str, $search, $case_insensitivity = false)
{
    if (strlen($str) < strlen($search)) {  //若第一个字符串长度小于第二个的时候，必须指定返回false，
        return false;                                   //否则substr_compare遇到这种情况会返回0（即相当，与事实不符合）
    } else {
        return !substr_compare($str, $search, strlen($str) - strlen($search), strlen($search), $case_insensitivity); //从第一个字符串减去第二个字符串长度处开始判断
    }
}

/**
 * 输出数据库登录表单
 * @since 1.0 2014-7-31 by sutroon
 */
function echoDbLoginForm()
{
    // 显示数据库登录表单
    echo '<form method="post" id="connform">';
    echo 'host:<input type="text" name="host" placeholder="服务器地址" required value="localhost" /><a href="#">[x]</a><br />';
    echo 'user:<input type="text" name="user" placeholder="登录账户" value="xcall" required /><a href="#">[x]</a><br />';
    echo 'password:<input type="text" name="password" placeholder="登录密码" value="root!@#xcall098)(*Admin%^&" /><a href="#">[x]</a><br />';
    echo 'database:<input type="text" name="database" placeholder="数据库名称" required /><a href="#">[x]</a><br />';
    echo '<input type="submit" value="Login" /> <input type="reset" value="Reset" />';
    echo '<input type="hidden" name="action" value="logindb" />';
    echo '</form>';
    echo '<script type="text/javascript">$("a").click(function(){$(this).prev().val("");return false;});</script>';
    die('<div>copyright 2014 by sutroon</div>');
}

// ==========通用函数2===============
/**
 * 获取字段定义单元格文本
 * @param type $row
 * @return type
 * @since 1.0 2014-7-29 by sutroon
 */
function get_td_field($row, $showCheckbox = false)
{
    return '<input type="' . ($showCheckbox ? 'checkbox' : 'hidden') . '" name="field" value="' . $row['Field'] . '" /> ' . $row['Field'] . ' ' . $row['Type'] . ' ' . ($row['Null'] == 'YES' ? ' null' : ' <strong style="color:#F00; font-weight:normal">not null</strong>') . ' ' . (is_null($row['Default']) ? '' : ' default ' . $row['Default']) . (is_null($row['Comment']) ? '' : ' comment \'' . $row['Comment'] . '\'');
}

/**
 * 获取与类型对应的字段值占位符
 * @param array $row 数据行
 * @param bool $fillDefault 填充默认值
 * @return string
 * @since 1.0 2014-8-23 by sutroon
 */
function get_type_field($row, $fillDefault = false)
{
    $type = get_field_alias_type($row['Type']);
    $value = ($row['Null'] == 'NO' && $fillDefault && !is_null($row['Default'])) ? $row['Default'] : $row['Field'];
    if ($value === 'CURRENT_TIMESTAMP') {
        $value = 'now()';
    } else {
        $value = $type == 'str' || $type == 'dat' ? '\'' . $value . '\'' : $value;
    }
    return $value;
}

/**
 * 获取与类型对应的字段值
 * @param array $row 数据行
 * @param bool $fillDefault 填充默认值
 * @return string
 * @since 1.0 2014-8-23 by sutroon
 */
function get_type_value($row, $fillDefault = false)
{
    $type = get_field_alias_type($row['Type']);
    $value = '';
    if ($row['Null'] == 'NO') {
        if (is_null($row['Default'])) {
            switch ($type) {
                case 'dat':
                    $value = '';
                    break;
                case 'flt':
                    $value = 0.00;
                    break;
                case 'int':
                    $value = 0;
                    break;
                default:
                    $value = '';
                    break;
            }
        } else {
            $value = $row['Default'];
        }
        if ($value === 'CURRENT_TIMESTAMP') {
            $value = 'now()';
        } else {
            $value = $type == 'str' || $type == 'dat' ? '\'' . $value . '\'' : $value;
        }
    } else {
        $value = 'NULL';
    }
    return $value;
}

/**
 * 判断字段类型的别名
 * @param string $type 数据库字段类型,如int(11),smallint(6)等
 * @return string 返回字段类型的别名,如int,flt,dat,str
 * @since 1.0 2014-9-2 by sutroon
 */
function get_field_alias_type($type)
{
    $arr = array(
        'int' => array('tinyint', 'smallint', 'mediumint', 'int', 'bigint'),
        'flt' => array('float', 'real', 'double', 'decimal', 'numeric'),
        'dat' => array('datetime', 'date', 'timestamp', 'time', 'year')
    );
    $lenType = 0;
    $lenv = 0;
    foreach ($arr as $k => $arrrow) {
        foreach ($arrrow as $v) {
            $lenType = strlen($type);
            $lenv = strlen($v);
            if ($lenType >= $lenv && substr($type, 0, $lenv) == $v) {
                return $k;
            }
        }
    }
    return 'str';
}

/**
 * 获取参数格式的名称
 * @param array $row
 * @param string $varprefix
 * @return string
 * @since 1.0 2014-8-23 by sutroon
 */
function get_var_field($row, $varprefix = 'p')
{
    return $varprefix . get_field_alias_type($row['Type']) . '_' . $row['Field'];
}

/**
 * 获取变量注释
 * @param array $row
 * @param string $varprefix
 * @return string
 * @since 1.0 2014-8-23 by sutroon
 */
function get_var_comment($row, $varprefix = 'p')
{
    $type = get_field_alias_type($row['Type']);
    $arr = array('int' => 'int', 'flt' => 'float', 'dat' => 'datetime', 'str' => 'string');
    return '* @param ' . $arr[$type] . ' $' . $varprefix . $type . '_' . $row['Field'] . ' ' . $row['Comment'];
}

/**
 * 获取将数据表字段转换为变量名称的文本串
 * @param string $table
 * @since 1.0 2014-8-27 by sutroon
 */
function get_table_varnames_text($table)
{
    $str = '';
    $arr = get_table_schema_array($table);
    foreach ($arr as $row) {
        $str .= get_var_field($row) . ', ';
    }
    if ($str) {
        $str = substr($str, 0, strlen($str) - 2);
    }
    return $str;
}

/**
 * 获取将数据表自动换转换为变量注释的文本串
 * @param string $table
 * @return string
 * @since 1.0 2014-8-27 by sutroon
 */
function get_table_varcomments_text($table)
{
    $str = '';
    $arr = get_table_schema_array($table);
    foreach ($arr as $row) {
        $str .= get_var_comment($row) . PHP_EOL;
    }
    if ($str) {
        $str = substr($str, 0, strlen($str) - 2);
    }
    return $str;
}

function get_customer_field_insert_text($table)
{
    $str = 'insert into tsys_custom_field (`field`, `type`, `length`, `null`, `default`, `comment`, sysLevel, `target`, `label`, `inputType`, `optionalValue`, `ordinal`, `isDisplay`) values ' . PHP_EOL;
    $i = 1;
    $arr = get_table_schema_array($table);
    foreach ($arr as $row) {
        $field = $row['Field'];
        $fieldlen = strlen($field);
        if ($i < 2 || $field == 'id' || $field == 'createdTime' || $field == 'exTags' || $field == 'siteID') {
            $i++;
            continue;
        }
        $str .= "('" . $row['Field'] . "', ";
        $type = $row['Type'];
        if (false !== ($pos = strpos($type, '('))) {
            $length = substr($type, $pos + 1, strlen($type) - $pos - 2);
            $type = substr($type, 0, $pos);
            if (false === ($pos2 = strpos($length, ','))) {
                $str .= "'$type', $length";
            } else {
                $pointLen = substr($length, $pos2 + 1);
                $length = substr($length, 0, $pos2);
                $str .= "'$type', $pointLen";
            }
        } else {
            $str .= "'$type', 0";
        }
        $label = $row['Comment'];
        if (false !== ($pos = strpos($label, '('))) {
            $optionalValue = substr($label, $pos + 1, strlen($label) - $pos - 2);
            $label = substr($label, 0, $pos);
            $inputType = 'select';
            if (substr_count($optionalValue, ',') < 2 && substr_count($optionalValue, '=') < 2) {
                $optionalValue = '';
                $inputType = 'text';
            }
        } else {
            $optionalValue = '';
            $inputType = 'text';
        }
        if ($type == 'datetime' || $type == 'time' || $type == 'date') {
            $inputType = $type;
        }
        if ($fieldlen > 5 && strtolower(substr($field, $fieldlen - 5)) == 'state') {
            $inputType = 'radio';
        }
        if ($fieldlen > 3 && substr($field, $fieldlen - 2) == 'ID') {
            $inputType = 'select';
        }
        if (false !== strpos(',thumbUrl,thumbUrl,pictureUrl,topImage,filePath,faceUrl,', ",$field,")) {
            $inputType = 'file';
        }

        $isDisplay = ($fieldlen > 5 && strtolower(substr($field, $fieldlen - 5)) == 'count') ? 0 : 1;
        $str .= ", '" . $row['Null'] . "', '" . $row['Default'] . "', '" . $row['Comment'] . "', 0, '[target]', '" . $label . "', '$inputType', '$optionalValue', $i, $isDisplay)," . PHP_EOL;
        $i++;
    }

    if ($str) {
        $str = substr($str, 0, strlen($str) - strlen(PHP_EOL) - 1);
    }
    $str .= ';' . PHP_EOL . "update tsys_custom_field set `target`='target', ordinal=id where target='[target]'";
    return $str . ';';
}

/**
 * 获取数据表的结构数组
 * @param string $table
 * @return array
 * @since 1.0 2014-8-27 by sutroon
 */
function get_table_schema_array($table)
{
    $result = mysql_query('SHOW FULL FIELDS FROM ' . $table); // 查询包含注释的字段定义
    $arr = null;
    while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
        $arr[] = $row;
    }
    return $arr;
}

/**
 * 获取表结构的文本表格
 * @param string $table
 * @return string
 * @since 1.0 2014-8-27 by sutroon
 */
function get_table_schema_text($table)
{
    $str = '<table cellspacing="0"><tr><th>序号</th><th class="th-field">字段定义</th></tr>';
    $i = 1;
    $arr = get_table_schema_array($table);
    foreach ($arr as $row) {
        $style = ($i % 2 == 0) ? ' class="tr-alt"' : '';
        $str .= '<tr' . $style . '><td>' . $i . '</td><td>' . get_td_field($row) . '</td></tr>';
        $i++;
    }
    return $str . '</table>';
}

/**
 * 显示创建对象的语句
 * 如数据表、视图、存储过程、函数
 * @param $object 对象名称
 * @param string $type 类型，支持TABLE,VIEW,PROCEDURE,FUNCTION
 * @return string
 * @since 1.0 2014-9-26 by sutroon
 * @example get_create_object_sql('proc_test','PROCEDURE');
 */
function get_create_object_sql($object, $type = 'TABLE')
{
    switch (strtolower($type)) {
        case 'view':
            $script = 'Create View';
            break;
        case 'procedure':
            $script = 'Create Procedure';
            break;
        case 'function':
            $script = 'Create Function';
            break;
        default:
            $script = 'Create Table';
            break;
    }
    $result = mysql_query("Show $script $object;");
    $row = mysql_fetch_array($result, MYSQLI_ASSOC);
    if (!isset($row[$script])) {
        die($object . '::' . $type);
    }
    return $result ? $row[$script] : '';
}

require 'incSqlGenerateFunc.php';
